#!/usr/bin/env python
# -*- coding: utf-8 -*-

import sys
import dianputools


def get_comment_stats(y, m, d, h=None):
    dtstart, dtend = dianputools.str_day_start_and_end(y, m, d, h)

    dbh = dianputools.connect_to_dianpu()
    query = 'select type from comment where date >= "%s" and date < "%s";' % (dtstart, dtend)
    # print query
    rows = dianputools.query(dbh, query)

    if rows is None or len(rows) == 0:
        return 0, 0, 0, 0, 0

    comment = [r[0] for r in rows]
    ncomment = len(comment)

    # 1 for good, 2 middle, 3 for bad
    ngood = len(filter(lambda x: x == '1', comment))
    rgood = float(ngood) / ncomment
    nbad = len(filter(lambda x: x == '3', comment))
    rbad = float(nbad) / ncomment

    return ncomment, ngood, nbad, rgood, rbad


def get_orderbook_stats(y, m, d, h=None):
    dtstart, dtend = dianputools.str_day_start_and_end(y, m, d, h)

    querys = []

    # the num of booking product
    query = 'select count(distinct productid)  from t_orderproduct op left outer join  t_order o on o.orderid=op.orderid where o.createdate > "%s" and o.createdate < "%s";' % (dtstart, dtend)
    querys.append(query)

    # the num of booking order
    query = 'select count(1) from t_orderproduct op left outer join  t_order o on o.orderid=op.orderid where o.createdate > "%s" and o.createdate < "%s";' % (dtstart, dtend)
    querys.append(query)

    # the num of booking order fee
    query = 'select sum(price) from t_orderproduct op left outer join  t_order o on o.orderid=op.orderid where o.createdate > "%s" and o.createdate < "%s";' % (dtstart, dtend)
    querys.append(query)

    dbh = dianputools.connect_to_dingdan()
    nums = [dianputools.get_a_number(dbh, q) for q in querys]

    return nums


def get_order_stats(y, m, d, h=None):
    dtstart, dtend = dianputools.str_day_start_and_end(y, m, d, h)

    querys = []

    # the num of order
    query = 'select count(1) from t_order where createdate>"%s" and createdate<"%s";' % (dtstart, dtend)
    querys.append(query)

    # the num of payed order
    query = 'select count(1) from t_order where state in(3,4,5,8) and createdate > "%s" and createdate < "%s";' % (dtstart, dtend)
    querys.append(query)

    # the num of user payed
    query = 'select count(distinct buyerid) from t_order where state in(3,4,5,8) and createdate > "%s" and createdate < "%s";' % (dtstart, dtend)
    querys.append(query)

    # the num of new user payed
    query = 'select count(1) from (select buyerid from t_order where state in(3,4,5,8) group by buyerid having min(date(createdate))="%s") tmp;' % (dtstart)
    querys.append(query)

    # total fee
    query = 'select sum(ordertotal) from t_order where state in(3,4,5,8) and createdate > "%s" and createdate < "%s";' % (dtstart, dtend)
    querys.append(query)

#    # commission
#    query = 'select count(1) from (select buyerid from t_order where paydate is not null group by buyerid having min(date(paydate))="%s") tmp;' % (dtstart)
#    querys.append(query)

    dbh = dianputools.connect_to_dingdan()
    nums = [dianputools.get_a_number(dbh, q) for q in querys]

    # get the old user paid
    oldpaiduser = nums[2] - nums[3]
    nums.insert(3, oldpaiduser)

    return nums


def get_orderticket_stats(y, m, d, h=None):
    dtstart, dtend = dianputools.str_day_start_and_end(y, m, d, h)

    querys = []

    # the num of ticket
    query = 'select count(1) from t_orderticket where tickettype=0 and lifestate=0 and state=0 and consumedate > "%s" and consumedate < "%s";' % (dtstart, dtend)
    querys.append(query)

    # the num of order
    query = 'select count(1) from t_ticketlog where cashdate > "%s" and cashdate < "%s";' % (dtstart, dtend)
    querys.append(query)

    # fee
    query = 'select sum(op.price) from t_orderproduct op left outer join t_orderticket ot on op.id=ot.orderproductid left outer join t_ticketlog otl on ot.id=otl.ticketid where otl.cashdate > "%s" and otl.cashdate < "%s";' % (dtstart, dtend)
    querys.append(query)

#    # commission
#    query = 'select count(1) from t_orderticket where tickettype=0 and lifestate=0 and state=0 and consumedate>"%s" and consumedate<"%s";' % (dtstart, dtend)
#    querys.append(query)

    # order back before ticket checking
    query = 'select count(1) from t_orderproduct op left outer join t_order o on o.orderid=op.orderid where o.createdate > "%s" and o.createdate < "%s" and op.refundstate=3 and op.resumestate=0;' % (dtstart, dtend)
    querys.append(query)

    # user back before ticket checking
    query = 'select count(distinct op.buyerid) from t_orderproduct op left outer join t_order o on o.orderid=op.orderid where o.createdate > "%s" and o.createdate < "%s" and op.refundstate=3 and op.resumestate=0;' % (dtstart, dtend)
    querys.append(query)

    # fee back before ticket checking
    query = 'select sum(op.price)  from t_orderproduct op left outer join t_order o on o.orderid=op.orderid where o.createdate > "%s" and o.createdate < "%s" and op.refundstate=3 and op.resumestate=0;' % (dtstart, dtend)
    querys.append(query)

    # order back after ticket checking
    query = 'select count(1) from t_orderproduct op left outer join t_order o on o.orderid=op.orderid where o.createdate > "%s" and o.createdate < "%s" and op.refundstate=3 and op.resumestate=1;' % (dtstart, dtend)
    querys.append(query)

    # user back after ticket checking
    query = 'select count(distinct op.buyerid) from t_orderproduct op left outer join t_order o on o.orderid=op.orderid where o.createdate > "%s" and o.createdate < "%s" and op.refundstate=3 and op.resumestate=1;' % (dtstart, dtend)
    querys.append(query)

    # fee back after ticket checking
    query = 'select sum(op.price)  from t_orderproduct op left outer join t_order o on o.orderid=op.orderid where o.createdate > "%s" and o.createdate < "%s" and op.refundstate=3 and op.resumestate=1;' % (dtstart, dtend)
    querys.append(query)

    dbh = dianputools.connect_to_dingdan()
    nums = [dianputools.get_a_number(dbh, q) for q in querys]

    return nums


def get_product_stats(y, m, d, h=None):
    dtstart, dtend = dianputools.str_day_start_and_end(y, m, d, h)

    # online product
    dbh = dianputools.connect_to_shangpin()
    query = 'select count(1) from t_product where salestate=1;'
    online_product = dianputools.get_a_number(dbh, query)

    # the product exchanged
    dbh = dianputools.connect_to_dingdan()
    query = 'select count(distinct productid)from t_orderproduct where settlementtime > "%s" and settlementtime < "%s"' % (dtstart, dtend)
    exchanged_product = dianputools.get_a_number(dbh, query)

    return online_product, exchanged_product


def get_visitor_stats_from_ww(day, hour=None):
    dbh = dianputools.connect_to_50()
    flux_table = 'shop_flux_day' if hour is None else 'shop_flux_hour'
    q = 'select sum(pv),sum(visits),sum(uv),sum(pv)/sum(visits),sum(new_visits),sum(new_visits)/sum(visits),sum(new_uv),sum(new_uv)/sum(uv),sum(time_on_site),sum(time_on_site)/sum(uv),sum(bounce_count),sum(bounce_count)/sum(pv),sum(register_count),sum(login_count) from %s where stat_date="%s"' % (flux_table, day)
    if hour is not None:
        q += 'and stat_hour="%s"' % (hour)
    # print q
    field = ['pv', 'visits', 'uv', 'pv_per_uv', 'newpv', 'newpvratio', 'newvisitor', 'newvisitorratio', 'staytime', 'avgstaytime', 'bound', 'boundratio', 'userregister', 'userlogin']
    try:
        sumflux = dianputools.query(dbh, q)[0]
    except:
        sumflux = [0] * len(field)

    sumflux = map(lambda x: x if x is not None else 0, sumflux)
    return field, sumflux

#     restriction = 'day="%s"' % (day)
#     if hour is not None:
#         restriction += 'and hour="%s"' % (hour)
#     table = 'kpi_day' if hour is None else 'kpi_hour'
#     dianputools.update(dbh, table, field, sumflux, restriction)
#
#
#     print 'TODO: get data from ww'
#     pass


def get_stats_for_all(year, month, day, hour=None):
    DEBUG = True

    dtstart, dtend = dianputools.str_day_start_and_end(year, month, day)

    tablename = 'kpi_day'
    fields = ['day']
    values = [dtstart]
    if hour is not None:
        tablename = 'kpi_hour'
        fields.append('hour')
        values.append(str(hour))

#     bookingproduct, bookingorder, bookingfee = get_orderbook_stats(year, month, day, hour)
#     if DEBUG: print 'orderbook stats: ', bookingproduct, bookingorder, bookingfee
    fields.extend(['bookingproduct', 'bookingorder', 'bookingorderfee'])
    values.extend(get_orderbook_stats(year, month, day, hour))


#     ncomment, ngood, nbad, rgood, rbad = get_comment_stats(year, month, day, hour)
#     if DEBUG: print 'comment: ', ncomment, ngood, nbad, rgood, rbad
    fields.extend(['comment', 'goodcomment', 'badcomment', 'goodcommentratio', 'badcommentratio'])
    values.extend(get_comment_stats(year, month, day, hour))



    norder, npay, numuser, nolduser, nnewuser, fee = get_order_stats(year, month, day, hour)
#     if DEBUG: print 'order stats: ', norder, npay, numuser, nolduser, nnewuser, fee
    fields.extend(['norder', 'paidorder', 'paiduser', 'oldpaiduser', 'newpaiduser', 'fee'])
    values.extend(get_order_stats(year, month, day, hour))



    ticket, order, ticket_fee, orderbackbefore, userbackbefore, feebackbefore, orderbackafter, userbackafter, feebackafter = get_orderticket_stats(year, month, day, hour)
    fields.extend(['consumedticket', 'consumedorder', 'consumedfee', 'refundorderbeforeticket', 'refunduserbeforeticket', 'refundfeebeforeticket', 'refundorderafterticket', 'refunduserafterticket', 'refundfeeafterticket'])
    values.extend(get_orderticket_stats(year, month, day, hour))


    ratebefore = 0 if npay == 0 else 1.0 * orderbackbefore / npay
    rateafter = 0 if npay == 0 else 1.0 * orderbackafter / npay
    fields.extend(['refundratiobeforeticket', 'refundratioafterticket'])
    values.extend([ratebefore, rateafter])
#     if DEBUG: print 'orderticket stats: ', ticket, order, ticket_fee, orderbackbefore, userbackbefore, feebackbefore, ratebefore, orderbackafter, userbackafter, feebackafter, rateafter


    # TODO:
    xxdesc, xxflux = get_visitor_stats_from_ww(dtstart, hour)
    fields.extend(xxdesc)
    values.extend(xxflux)
    visitor = int(xxflux[1])

    online, exchanged = get_product_stats(year, month, day, hour)
    fields.extend(['onlineproduct', 'exchangeproduct'])
    values.extend([online, exchanged])


    dongxiao_rate = 0 if online == 0 else 1.0 * exchanged / online
    transition_rate = 0 if visitor == 0 else npay / visitor
    fields.extend(['dongxiaolv', 'transitionrate'])
    values.extend([dongxiao_rate, transition_rate])
#     if DEBUG: print 'order stats: ', online, exchanged, dongxiao_rate, transition_rate


    fee_per_visit = 0 if visitor == 0 else 1.0 * fee / visitor  # TODO,
    fee_per_user = 0 if numuser == 0 else 1.0 * fee / numuser
    consume_rate = 0 if npay == 0 else 1.0 * ticket_fee / npay
#     if DEBUG: print 'last stats: ', fee_per_visit, fee_per_user, consume_rate

    fields.extend(['liushuipervisit', 'kedanjia'])
    values.extend([fee_per_visit, fee_per_user])



    dbh = dianputools.connect_to_50()
    dianputools.insertinto(dbh, tablename, fields, values)


if __name__ == '__main__':
    if len(sys.argv) != 4:
        # print 'usage: %s year month day' % (sys.argv[0])
        # sys.exit(1)
        year, month, day = 2012, 4, 16
    else:
        year, month, day = map(int, sys.argv[1:])
    print year, month, day

    get_stats_for_all(year, month, day)
